<?php
jimport('joomla.application.component.model');
class svModelCanbo extends JModel
{
	//------------ tim kiem----------------------------------
	function getAllCanbo($param)
	{
		$where="";
		$cl='  AND cb_id=0';
		$detail=$this->getDetailCanbo();
		if($detail->ac_typeaccount=='qlphong') $cl='  AND pb.pb_id='.$detail->pb_id;
		if($detail->ac_typeaccount=='admin') $cl='';
		if($detail->ac_typeaccount=='qldonvi') $cl='  AND pb.pb_iddonviquanly='.$detail->dvql_id;
		$clause=" SELECT  cb.*, qlc.*, cv.cv_ten,pb.pb_ten,cd.cd_ten
			    FROM    qlcb_canbo as cb,qlcb_quanlychung as qlc,qlcb_phongban as pb,qlcb_chucvu as cv, qlcb_chucdanh as cd
			    WHERE   cb.cb_id = qlc.qlc_idcanbo  
				AND		qlc.qlc_idphongban=pb.pb_id 
				AND		qlc.qlc_idchucvu=cv.cv_id 
				AND		qlc.qlc_idchucdanh=cd.cd_id  ";
		$donvi=JRequest::getInt('iddonvi');
		$adminn=$this->getAdmin();
		if($adminn!=null) $cl=''; 
		if($donvi!=0) $cl='  AND pb.pb_iddonviquanly='.$donvi.' ';
		$phongban=JRequest::getInt('idphongban');
		if($phongban!=0) $cl=' AND qlc.qlc_idphongban='.$phongban.' ';
		$canbo=JRequest::getInt('cb_id');
		if($canbo!=0) $cl='  AND cb.cb_id='.$canbo.' ';
		$query=$clause.' '.$cl.' ORDER BY '.$param['order'].'  '.$param['order_Dir'].'
				LIMIT '.$param['limitstart'].' , '.$param['limit'];
		$result=$this->_loadObjectList($query);
		return $result;
	}
	function getTotal()
	{
		$where="";
		$cl='  AND cb_id=0';
		$detail=$this->getDetailCanbo();
		if($detail->ac_typeaccount=='qlphong') $cl='  AND pb.pb_id='.$detail->pb_id;
		if($detail->ac_typeaccount=='admin') $cl='';
		if($detail->ac_typeaccount=='qldonvi') $cl='  AND pb.pb_iddonviquanly='.$detail->dvql_id;
		$clause=" SELECT  COUNT(cb.cb_id)
			    FROM    qlcb_canbo as cb,qlcb_quanlychung as qlc,qlcb_phongban as pb,qlcb_chucvu as cv, qlcb_chucdanh as cd
			    WHERE   cb.cb_id = qlc.qlc_idcanbo  
				AND		qlc.qlc_idphongban=pb.pb_id 
				AND		qlc.qlc_idchucvu=cv.cv_id 
				AND		qlc.qlc_idchucdanh=cd.cd_id  ";
		$donvi=JRequest::getInt('iddonvi');
		$adminn=$this->getAdmin();
		if($adminn!=null) $cl=''; 
		if($donvi!=0) $cl='  AND pb.pb_iddonviquanly='.$donvi.' ';
		$phongban=JRequest::getInt('idphongban');
		if($phongban!=0) $cl=' AND qlc.qlc_idphongban='.$phongban.' ';
		$canbo=JRequest::getInt('cb_id');
		if($canbo!=0) $cl='  AND cb.cb_id='.$canbo.' ';
		$query=$clause.' '.$cl.' ';
		$result=$this->_loadResult($query);
		return $result;
	}
	function getAdmin()
	{
		$acid=JFactory::getUser()->get('id');
		$query="SELECT * FROM #__users WHERE id=".$acid;
		$result=$this->_loadObject($query);
		return $result;
	}
	function getDetailCanbo()
	{
		$acid=JFactory::getUser()->get('id');
		$clause=" SELECT  ac.*,pb.*,dvql.*
			    FROM    qlcb_useraccount as ac,qlcb_quanlychung as qlc,qlcb_phongban as pb,qlcb_donviquanly as dvql 
			    WHERE   ac.ac_idcanbo = qlc.qlc_idcanbo  
				AND		qlc.qlc_idphongban=pb.pb_id 
				AND		dvql.dvql_id=pb.pb_iddonviquanly 
		 		AND		ac.ac_iduser=".$acid;
		$query=$clause;
		$result=$this->_loadObject($query);
		return $result;
	}
	//--------------- select list -----------
	function Listcanbo()
	{
		$where=" WHERE cb.cb_id=0";
		$adminn=$this->getAdmin();
		if($adminn!=null) $where='';
		$donvi=JRequest::getInt('iddonvi');
		if($donvi!=0) $where=',qlcb_quanlychung as qlc,qlcb_phongban as pb
		WHERE qlc.qlc_idcanbo = cb.cb_id
		AND  qlc.qlc_idphongban = pb.pb_id
		AND pb.pb_iddonviquanly='.$donvi;
		$phongban=JRequest::getInt('idphongban');
		if($phongban!=0) $where=',qlcb_quanlychung as qlc WHERE qlc.qlc_idcanbo=cb.cb_id AND qlc.qlc_idphongban='.$phongban;
		$query='SELECT DISTINCT cb.cb_id as `key`,cb.cb_ten as `text`  FROM qlcb_canbo as cb '.$where;
		return $this->_getList($query);
	}
	function ListPhongban()
	{
		$where="";
		$cl='  WHERE pb_id=0';
		$adminn=$this->getAdmin();
		if($adminn!=null) $cl='';
		$detail=$this->getDetailCanbo();
		if($detail->ac_typeaccount=='qlphong') $cl='  WHERE pb_id='.$detail->pb_id;
		if($detail->ac_typeaccount=='admin') $cl='';
		if($detail->ac_typeaccount=='qldonvi') $cl='';
		if($cl=='')
		{
			$donvi=JRequest::getInt('iddonvi');
			if($donvi!=0) $where=' AND pb_iddonviquanly='.$donvi;
		}

		$query='SELECT DISTINCT pb_id as `key`,pb_ten as `text`  FROM qlcb_phongban '.$cl.' '.$where;
		return $this->_getList($query);
	}
	function ListDonvi()
	{
		$cl='  WHERE dvql_id=0';
		$adminn=$this->getAdmin();
		if($adminn!=null) $cl='';
		$detail=$this->getDetailCanbo();
		if($detail->ac_typeaccount=='qldonvi') $cl='  WHERE dvql_id='.$detail->dvql_id;
		if($detail->ac_typeaccount=='admin') $cl='';
		$query='SELECT DISTINCT dvql_id as `key`,dvql_ten as `text`  FROM qlcb_donviquanly '.$cl;
		return $this->_getList($query);
	}
	
	//------------------------------
	function getCanboByID($idcanbo)
	{
		$query='SELECT  cb.*, qlc.*, cv.cv_ten as chucvu,pb.pb_ten as phongban,cd.cd_ten as chucdanh
				FROM    qlcb_canbo as cb,qlcb_quanlychung as qlc,qlcb_phongban as pb,qlcb_chucvu as cv, qlcb_chucdanh as cd
				WHERE   cb.cb_id = qlc.qlc_idcanbo
				AND		qlc.qlc_idphongban=pb.pb_id
				AND		qlc.qlc_idchucvu=cv.cv_id
				AND		qlc.qlc_idchucdanh=cd.cd_id  
				AND 	cb.cb_id='.$idcanbo;
		$result=$this->_loadObject($query);
		return $result;
	}
	function getCanboByIDs($idcanbo)
	{
		$query='SELECT  cb.cb_id,cb.cb_ten,cb.cb_namsinh,cb.cb_hinhanh,cb.cb_diachi,
		cv.cv_ten as chucvu,pb.pb_ten as phongban,cd.cd_ten as chucdanh
		FROM    qlcb_canbo as cb,qlcb_quanlychung as qlc,qlcb_phongban as pb,qlcb_chucvu as cv, qlcb_chucdanh as cd
		WHERE   cb.cb_id = qlc.qlc_idcanbo
		AND		qlc.qlc_idphongban=pb.pb_id
		AND		qlc.qlc_idchucvu=cv.cv_id
		AND		qlc.qlc_idchucdanh=cd.cd_id
		AND 	cb.cb_id='.$idcanbo;
		$result=$this->_loadObjectList($query);
		return $result;
	}
	function getAllPhongban()
	{
		$query='SELECT pb_id,pb_ten
				FROM qlcb_phongban
				WHERE 1';
		$result=$this->_loadObjectList($query);
		return $result;
	}
	function getAllChucvu()
	{
		$query='SELECT cv_id,cv_ten
		FROM qlcb_chucvu
		WHERE 1';
		$result=$this->_loadObjectList($query);
		return $result;
	}
	function getAllChucdanh()
	{
		$query='SELECT cd_id,cd_ten
		FROM qlcb_chucdanh
		WHERE 1';
		$result=$this->_loadObjectList($query);
		return $result;
	}
	//----------------------------them ,sua ,xoa -------------------------------
	function addNewCanbo($ten,$namsinh,$quequan,$cmnd,$gioitinh,$diachi,$dienthoai,$tongiao,$honnhan,$hinhanh,$mieuta,$dantoc)
	{
		$query="INSERT INTO qlcb_canbo(cb_ten,cb_namsinh,cb_quequan,cb_cmnd,cb_gioitinh,cb_diachi,
		cb_sodienthoai,cb_tongiao,cb_honnhan,cb_hinhanh,cb_mieuta,cb_dantoc)
		VALUES('".$ten."','".$namsinh."','".$quequan."','".$cmnd."','".$gioitinh."','".$diachi."','".$dienthoai."',
		'".$tongiao."','".$honnhan."','".$hinhanh."','".$mieuta."','".$dantoc."')";
		$message='Thêm mới ';
		$result=$this->_Execute($query, $message);
		return $result.' <b style="font-size:18px;color:blue;">'.$ten.'</b>';
	}
	function addNewQuanlychung($idphongban,$idchucvu,$idchucdanh,$idcanbo)
	{
		$timeupdate=JFactory::getDate()->toMysql();
		$idupdate=JFactory::getUser()->get('id');
		$query="INSERT INTO qlcb_quanlychung(qlc_idphongban,qlc_idchucvu,qlc_idchucdanh,qlc_idcanbo,timeupdate,idupdate)
		 VALUES('".$idphongban."','".$idchucvu."','".$idchucdanh."','".$idcanbo."','".$timeupdate."','".$idupdate."')";
		$message='Thêm mới ';
		$result=$this->_Execute($query, $message);		
		return $result;
	}
	function getMaxIDCanbo()
	{
		$query='SELECT MAX(cb_id) as max
		FROM qlcb_canbo
		WHERE 1';
		$result=$this->_loadObject($query);
		return $result->max;
	}
	// sua
	function editCanbo($idcanbo,$ten,$namsinh,$quequan,$cmnd,$gioitinh,$diachi,$dienthoai,$tongiao,$honnhan,$hinhanh,$mieuta,$dantoc)
	{
		$query="UPDATE qlcb_canbo 
				SET cb_mieuta='".$mieuta."', cb_ten='".$ten."', cb_ten='".$ten."', cb_namsinh='".$namsinh."',
					cb_quequan='".$quequan."', cb_cmnd='".$cmnd."', cb_gioitinh='".$gioitinh."', cb_diachi='".$diachi."',
					cb_sodienthoai='".$dienthoai."', cb_tongiao='".$tongiao."', cb_honnhan='".$honnhan."', cb_hinhanh='".$hinhanh."', cb_dantoc='".$dantoc."'
	   		    WHERE cb_id=".$idcanbo;
		$message='Chỉnh sửa ';
		$result=$this->_Execute($query, $message);
		return $result.' <b style="font-size:18px;color:blue;">'.$ten.'</b>';
	}
	function editQuanlychung($idphongban,$idchucvu,$idchucdanh,$idqlc)
	{
		$timeupdate=JFactory::getDate()->toMysql();
		$idupdate=JFactory::getUser()->get('id');
		$query="UPDATE qlcb_quanlychung 
		SET qlc_idphongban='".$idphongban."', qlc_idchucvu='".$idchucvu."', qlc_idchucdanh='".$idchucdanh."'
		, timeupdate='".$timeupdate."', idupdate='".$idupdate."' 
		WHERE qlc_id=".$idqlc;
		$message='Chỉnh sửa 1 item ';
		$result=$this->_Execute($query, $message);
		return $result;
	}
	function delChucdanh($idchucvu)
	{
		$query="DELETE FROM qlcb_chucdanh WHERE cd_id=".$idchucvu;
		$message='Xóa 1 item ';
		$result=$this->_Execute($query, $message);
		return $result;	
	}
	// cac ham phuc vu truy van CSDL
	function _loadResult($query)
	{
		$db= JFactory::getDBO();
		$db->setQuery($query);
		$result=$db->loadResult();
		return $result;
	}
	function _loadObject($query)
	{
		$db= JFactory::getDBO();
		$db->setQuery($query);
		$result=$db->loadObject();
		return $result;
	}
	function _loadObjectList($query)
	{
		$db= JFactory::getDBO();
		$db->setQuery($query);
		$result=$db->loadObjectList();
		return $result;
	}
	function _Execute($query,$message)
	{
		$db= JFactory::getDBO();
		$db->setQuery($query);
		try {
			$db->query();
			return $message." thành công ";
		}
		catch (Exception $e){
			return $message." Không thành công! Vui lòng thử lại.";
		}
	}
}
?>